<?php
namespace Crm\Table;

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Update;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Expression;
class OrderTable extends AbstractTable
{
    protected $table = 'hy_order';
    protected $id = 'order_id';
    
    
    public function getOrderList($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
        
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id ') ,
            $select::JOIN_LEFT
            );
        
        
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
        
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['status'])) {
            $select->where(array('o.status' => $query['status']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['order_type'])) {
            $select->where(array('o.order_type' => $query['order_type']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        
        $select->order('o.order_create_time DESC');
                 
        return $this->fetchPaginate($select,$query);
    }
    public function getOrderListAll($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
        
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id ') ,
            $select::JOIN_LEFT
            );
        
        
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
        
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['status'])) {
            $select->where(array('o.status' => $query['status']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['order_type'])) {
            $select->where(array('o.order_type' => $query['order_type']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        
        $select->order('o.order_create_time DESC');
                 
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }

    public function getCancelOrderList($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
        
        $select->where(array('o.cancel_apply' => 1));
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        
        $select->order('o.order_create_time DESC');
                 
        return $this->fetchPaginate($select,$query);
    }
    public function getByOrderId($order_id)
    {
        $rowset = $this->select(array('order_id' => $order_id));
        $row = $rowset->current();
        return $row;
    }
    public function updateItem($id,$data){
        if(empty($id)){
            throw new \Exception("id field is invalid!");
        }
        $item = array(
            'status'=>$data['status'],
            'address_info'=>$data["address_info"],
            'receiver_name'=>$data["receiver_name"],
            'cellphone'=>$data["cellphone"],
            //'user_id'=>$data["user_id"],
            'receiver_mark'=>$data["receiver_mark"],
            'send_time'=>$data["send_time"],
            'order_mark'=>$data["order_mark"],
            'express_no'=>$data["express_no"],
            'express_name'=>$data["express_name"],
            'order_update_time'=>new Expression('NOW()'),
        );
        return $this->update($item,array('order_id'=>$id));
    }
    public function sendOrder($id,$data){
        if(empty($id)){
            throw new \Exception("id field is invalid!");
        }
        $item = array(
            'status'=>3,
            'express_no'=> empty($data['express_no'])?new Expression('NULL'):$data['express_no'],
            'express_name' => empty($data['express_name'])?new Expression('NULL'):$data['express_name'],
            'send_time' => new Expression('NOW()'),
        );
        return $this->update($item,array('order_id'=>$id));
    }
    public function cancelOrder($id){
        if(empty($id)){
            throw new \Exception("id field is invalid!");
        }
        $item = array(
            'status'=>4,
            'cancel_apply'=> 2,
            'order_update_time'=>new Expression('NOW()'),
        );
        return $this->update($item,array('order_id'=>$id));
    }

    public function getOutExcelList($query = array())
    {
        
        $select = new Select(array('o' =>$this->getTable()));
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_name','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id ') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('u' => 'hy_user'),
            'u.user_id = o.user_id',
            array('user_name') ,
            $select::JOIN_LEFT
            );
        
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
        
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['status'])) {
            $select->where(array('o.status' => $query['status']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['order_type'])) {
            $select->where(array('o.order_type' => $query['order_type']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->order('o.order_id');
         
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }
    public function getOutExcelDetailList($query = array())
    {
        
        $select = new Select(array('o' =>$this->getTable()));
        $select->columns(array('order_no'));
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_name','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id ') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('oi' => 'hy_orderitem'),
            'oi.order_id = o.order_id',
            array('product_id','oi_name','oi_price','oi_amount','oi_meal_send_time','oi_meal_type') ,
            $select::JOIN_LEFT
            );
        
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
        
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['status'])) {
            $select->where(array('o.status' => $query['status']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['order_type'])) {
            $select->where(array('o.order_type' => $query['order_type']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->order('o.order_id');
         
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }
    public function getOutExcelProList($query = array())
    {
        
        $select = new Select(array('o' =>$this->getTable()));
        $select->columns(array('count'=>new Expression('SUM(oi.oi_amount)')));
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array() ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array(),
            $select::JOIN_LEFT
            );
        $select->join(
            array('oi' => 'hy_orderitem'),
            'oi.order_id = o.order_id',
            array('product_id','oi_name','oi_meal_send_time','oi_meal_type') ,
            $select::JOIN_LEFT
            );
        
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
        
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['status'])) {
            $select->where(array('o.status' => $query['status']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['order_type'])) {
            $select->where(array('o.order_type' => $query['order_type']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
        if(!empty($query['order_no'])) {
            $select->where(array('o.order_no' => $query['order_no']));
        }
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->group(array('oi.oi_meal_send_time','oi.oi_meal_type','oi.product_id','oi.oi_name'));
        $select->order(array('oi.oi_meal_send_time','oi.oi_meal_type','oi.product_id'));
         //die($select->getSqlString());
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }
    
    
    
    /**
     * 结算管理订单信息取得
     * @param array $query
     */
    public function getOrderJiesuList($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
    
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id','catalog_name') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('ct' => 'hy_city'),
            'ct.city_id = cata.city_id',
            array('city_name') ,
            $select::JOIN_LEFT
            );
    
    
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
    
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
    
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->where(array('o.status' => 3));
        $select->order('o.order_create_time DESC');
         
        return $this->fetchPaginate($select,$query);
    }
    public function getExcelJiesuList($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
    
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('catalog_id','org_type') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array('city_id','catalog_name') ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('ct' => 'hy_city'),
            'ct.city_id = cata.city_id',
            array('city_name') ,
            $select::JOIN_LEFT
            );
    
    
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
    
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
    
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->where(array('o.status' => 3));
        $select->order('o.order_create_time DESC');
         
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }
    public function getExcelJiesuPro($query = array())
    {
        $select = new Select(array('o' =>$this->getTable()));
        $select->columns(array('count'=>new Expression('SUM(oi.oi_amount)')));
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array() ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('cata' => 'hy_catalog'),
            'cata.catalog_id = org.catalog_id',
            array() ,
            $select::JOIN_LEFT
            );
        $select->join(
            array('oi' => 'hy_orderitem'),
            'oi.order_id = o.order_id',
            array('product_id','oi_name') ,
            $select::JOIN_LEFT
            );
    
    
        $where = new Where();
        if (!empty($query['end_time'])&&!empty($query['start_time'])) {
            $where->between('o.order_create_time', $query['start_time'], $query['end_time']);
        } elseif (!empty($query['end_time'])) {
            $where->lessThan('o.order_create_time', $query['end_time']);
        } elseif (!empty($query['start_time'])){
            $where->greaterThan('o.order_create_time', $query['start_time']);
        }
        $select->where($where);
    
        if(!empty($query['org_id'])) {
            $select->where(array('o.org_id' => $query['org_id']));
        }
        if(!empty($query['catalog_id'])) {
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }
        if(!empty($query['city_id'])) {
            $select->where(array('cata.city_id' => $query['city_id']));
        }
    
        if(!empty($_SESSION['account']['org_id'])) {
            $select->where(array('o.org_id' => $_SESSION['account']['org_id']));
        }
        $select->where(array('o.status' => 3));
        $select->group(array('oi.product_id','oi.oi_name'));
        $select->order(array('oi.product_id'));
         
        $rowset = $this->selectWith($select);
        $list = $rowset->toArray();
    
        return $list;
    }
    
    /**
     * 监管平台
     */
    public function findAllSupervision($query = array())
    {
    
        $select = new Select(array(
            'o' => $this->getTable()
        ));
        $select->columns(array(
            'order_id',
            //'org_name',
            'address_info',
            'receiver_name',
            'cellphone',
            'order_create_time'
            
        ));
        
        
        $select->join(
            array('org' => 'hy_organization'),
            'o.org_id = org.org_id',
            array('org_name') ,
            $select::JOIN_LEFT
            );
        
        $select->join(
            array('catalog' => 'hy_catalog'),
            'catalog.catalog_id = org.catalog_id',
            array('catalog_name') ,
            $select::JOIN_LEFT
            );
       
        
        if(!empty($query['catalog_id'])) {
            
            $select->where(array('org.catalog_id' => $query['catalog_id']));
        }

        $select->order('o.order_id DESC');
    
        return $this->fetchPaginate($select, $query);
    }
}